package persistence.employee.employeeDAO;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import persistence.DAO.DAOHelper;
import business.employee.entities.Employee;

public class EmployeeDAO {

	private static EmployeeDAO instance = null;

	private EmployeeDAO() {
	}

	public static EmployeeDAO getInstance() {
		if (instance == null)
			instance = new EmployeeDAO();
		return instance;
	}

	public Employee[] getEmployees()  {
		Connection con=null;
		try {
			con=DAOHelper.getInstance().getConnection();
			Statement st=con.createStatement();
			ResultSet rs=st.executeQuery("SELECT name, lastName, id_user_type,CI FROM Employee WHERE active=1");
			
			ArrayList<Employee> emp=new ArrayList<Employee>();
			while (rs.next()){
				emp.add(new Employee(rs.getString(1), rs.getString(2), rs.getInt(4), rs.getInt(3)));
			}
			Employee[] em=emp.toArray(new Employee[0]);
			rs.close();
			return em;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			if(con!=null)
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}	
	  }
		return null;
	}

	public Employee getEmployee(int id) {
		Connection con=null;
		try {
			con=DAOHelper.getInstance().getConnection();
			Statement st=con.createStatement();
			ResultSet rs=st.executeQuery("SELECT name, lastName, id_user_type,CI FROM Employee WHERE active=1 AND CI="+String.valueOf(id));
			
			if(!rs.next()) return null;
			Employee e=new Employee(rs.getString(1), rs.getString(2), rs.getInt(4), rs.getInt(3));
			rs.close();
			return e;
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			if(con!=null)
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}	
	  }
		return null;
	}

	public void update(Employee emp) {
		String sql = "UPDATE Employee SET ";
		sql = sql + "name='" + emp.getName() + "'";
		sql = sql + ", lastName='" + emp.getLastName();
		sql = sql + "', id_user_type="+emp.getEmployeeType();
		try {
			sql = sql + ", pass='"+DAOHelper.md5(emp.getPassword());
		} catch (Exception e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		sql = sql + "', CI="+emp.getId();
		sql = sql + "' WHERE CI="
				+ String.valueOf(emp.getId());
		Connection con=null;
		try {
			con=DAOHelper.getInstance().getConnection();
			Statement st=con.createStatement();
			st.executeUpdate(sql);
			

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(con!=null)
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}	
	  }
		
	}

	public void delete(int id) {
		String sql = "UPDATE Employee SET ";
		sql = sql + "active=0";
		sql = sql + " WHERE CI="
				+ String.valueOf(id);
		Connection con=null;
		try {
			con=DAOHelper.getInstance().getConnection();
			Statement st=con.createStatement();
			st.executeUpdate(sql);
			

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(con!=null)
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}	
	  }
			
	}

	public void add(Employee emp) {
	
		String sql;
		Connection con=null;
		try {
			con=DAOHelper.getInstance().getConnection();
			Statement st=con.createStatement();
			sql = "INSERT INTO Employee (name,lastName,pass,id_user_type,CI) VALUES ('"
					+ emp.getName()
					+ "','"
					+ emp.getLastName()
					+ "','"
					+ DAOHelper.md5(emp.getPassword())
					+ "',"
					+ String.valueOf(emp.getEmployeeType())
					+ ","
					+ String.valueOf(emp.getId())
					+ ")";
			
			st.execute(sql);
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(con!=null)
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}	
	  }
	}

	public boolean authenticate(Employee emp, String pass) {//pendiente
		Connection con=null;
		try {
			con=DAOHelper.getInstance().getConnection();
			Statement st=con.createStatement();
			ResultSet rs= st.executeQuery("SELECT pass FROM Employee WHERE CI="+emp.getId());
			rs.next();
			return DAOHelper.md5(pass).equals(rs.getString(1));
		}  catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(con!=null)
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}	
	  }
		return true;
	}

}
